﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace cmsservice.DAL
{
    public class EMNewsInfoDAL
    {
        delegate DataTable EMNewsInfo(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters);
        EMNewsInfo instance = new EMNewsInfo(SqlHelper.ExecuteGetTable);

        #region 查询EM_GRAB_QIANLONG_ZX表中的资讯新闻信息
        /// <summary>
        /// 根据发布时间获取所有最新的数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllNewsInfo(DateTime publDate)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select NewsId,NewsTitle,ZXType,NewsContent,PublDate,CreateDate from EM_GRAB_QIANLONG_ZX ");
            sql.Append(" where NewsId is not null and NewsTitle is not null and CreateDate>=@PublDate and Audit='1' and ZXType in('大盘分析','基金看市','b股','港股行情','港股','基金动态','基金评述','股指期货','创业板相关','动态与评述')");
            sql.Append(" order by PublDate desc,NewsId desc ");
            SqlParameter[] values = { new SqlParameter("@PublDate", publDate) };
            return instance(DBConn.NewDMSConnstr, CommandType.Text, sql.ToString(), values);
        }

        /// <summary>
        /// 个股资讯
        /// </summary>
        /// <param name="code">股票代码</param>
        /// <param name="top"></param>
        /// <returns></returns>
        public DataTable GetAllNewsInfo(string code)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select top 5 NewsId=Number,NewsTitle=title,ZXType='公司研究',NewsContent=content,PublDate=ReportTime,CreateDate=CreateTime into #tempA from EM_Grab_gaotime_zx where title is not null and Number is not null and [column]='公司研究' and Audit='1' and SecCode=@code order by ReportTime desc,CreateTime desc ");
            sql.Append("select top 2 NewsId=id,NewsTitle=case_summary,ZXType=reason,NewsContent=case_content,PublDate=abnormal_date,CreateDate=modify_time into #tempB from STOCK_ABNORMAL_MSG where case_summary is not null and id is not null and stock_id=@code order by abnormal_date desc,modify_time desc ");
            sql.Append("select * from #tempA union select * from #tempB ");
            sql.Append("drop table #tempA drop table #tempB");
            SqlParameter[] values = {
                                        new SqlParameter("@code",code)
                                    };
            return instance(DBConn.NewDMSConnstr, CommandType.Text, sql.ToString(), values);
        }

        /// <summary>
        /// 获取所有有资讯的股票代码
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllNewsCode()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" select code=stock_id from STOCK_ABNORMAL_MSG where len(stock_id)=6 union select code=SecCode from EM_Grab_gaotime_zx where [column]='公司研究' and Audit='1' and len(SecCode)=6 order by code ");
            return instance(DBConn.NewDMSConnstr, CommandType.Text, sql.ToString());
        }

        /// <summary>
        /// 盯盘先锋》主力追踪》市场全景》资讯》每日股评 ZXType IN('大盘分析','基金看市')
        /// </summary>
        /// <returns></returns>
        public DataTable GetEveryDayNews(string top)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            if (!string.IsNullOrEmpty(top))
                sql.Append(" top " + top + " ");
            sql.Append(" NewsId,NewsTitle,ZXType,PublDate,CreateDate from EM_GRAB_QIANLONG_ZX ");
            sql.Append(" where NewsId is not null and NewsTitle is not null and ZXType IN('大盘分析','基金看市') and Audit='1' and PublDate>='2009-1-1' ");
            sql.Append(" order by PublDate desc,NewsId desc ");
            return instance(DBConn.NewDMSConnstr, CommandType.Text, sql.ToString());
        }

        /// <summary>
        /// 盯盘先锋》行情速递》分类排名》沪B股/深B股>行业资讯 ZXType IN('b股')
        /// </summary>
        /// <returns></returns>
        public DataTable GetBGStockNews(string top)
        {
            return instance(DBConn.NewDMSConnstr, CommandType.Text, GetSqlStr("'b股'", top));
        }

        /// <summary>
        /// 盯盘先锋》基金页面/行情速递》分类排名》沪深基金》行业资讯 ZXType IN('基金动态'.'基金评述')
        /// </summary>
        /// <returns></returns>
        public DataTable GetJJStockNews(string top)
        {
            return instance(DBConn.NewDMSConnstr, CommandType.Text, GetSqlStr("'基金动态','基金评述'", top));
        }

        /// <summary>
        /// 盯盘先锋》行情速递》分类排名》H股/港股》行业资讯 ZXType IN('港股行情','港股')
        /// </summary>
        /// <returns></returns>
        public DataTable GetGGStockNews(string top)
        {
            return instance(DBConn.NewDMSConnstr, CommandType.Text, GetSqlStr("'港股行情','港股'", top));
        }

        /// <summary>
        /// 盯盘先锋》行情速递》股指期货》行业资讯 ZXType IN('股指期货')
        /// </summary>
        /// <returns></returns>
        public DataTable GetGZQHStockNews(string top)
        {
            return instance(DBConn.NewDMSConnstr, CommandType.Text, GetSqlStr("'股指期货'", top));
        }

        /// <summary>
        /// 盯盘先锋》创业板（159915）》行业资讯、个股资讯 ZXType IN('创业板相关')
        /// </summary>
        /// <returns></returns>
        public DataTable GetCYBStockNews(string top)
        {
            return instance(DBConn.NewDMSConnstr, CommandType.Text, GetSqlStr("'创业板相关'", top));
        }

        /// <summary>
        /// 盯盘先锋》中小板（159902）》行业资讯、个股资讯 ZXType IN('动态与评述')
        /// </summary>
        /// <returns></returns>
        public DataTable GetDTPSStockNews(string top)
        {
            return instance(DBConn.NewDMSConnstr, CommandType.Text, GetSqlStr("'动态与评述'", top));
        }

        /// <summary>
        /// 盯盘先锋》深100ETF（159901）、深成ETF（159903）、深红利（159905）、深成长（159906）、中小300（159907）、深F200（159908）、深TMT（159909）、深F120（159910）、民营ETF（159911）、深300ETF（159912）、深价值（159913）、深F60（159916）》行业资讯、个股资讯 ZXType IN('大盘分析','基金看市')
        /// </summary>
        /// <returns></returns>
        public DataTable GetDPJJStockNews(string top)
        {
            return instance(DBConn.NewDMSConnstr, CommandType.Text, GetSqlStr("'大盘分析','基金看市'", top));
        }

        /// <summary>
        /// 大盘分析
        /// </summary>
        /// <param name="top"></param>
        /// <returns></returns>
        public DataTable GetDPFXStockNews(string top)
        {
            return instance(DBConn.NewDMSConnstr, CommandType.Text, GetSqlStr("'大盘分析'", top));
        }

        /// <summary>
        /// 拼接EM_GRAB_QIANLONG_ZX表的查询SQL
        /// </summary>
        /// <param name="whereStr">条件参数,eg:'大盘分析','基金看市'</param>
        /// <returns></returns>
        string GetSqlStr(string whereStr, string top)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            if (!string.IsNullOrEmpty(top))
            {
                sql.Append(" top " + top + " ");
            }
            sql.Append(" NewsId,NewsTitle,ZXType,PublDate,CreateDate from EM_GRAB_QIANLONG_ZX ");
            sql.Append(" where NewsId is not null and NewsTitle is not null and ZXType IN(" + whereStr + ") and Audit='1' and PublDate>='2009-1-1' ");
            sql.Append(" order by PublDate desc,NewsId desc ");
            return sql.ToString();
        }

        #endregion

        #region 获取环球股指，外汇，大宗商品
        /// <summary>
        /// 获取环球股指，外汇，大宗商品
        /// </summary>
        /// <returns></returns>
        public DataTable GetGZWHSPPrice()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select distinct name=f003v_11501,zxz=(select top 1 f005n_11501 from tb_trade_11501 where f003v_11501=a.f003v_11501 order by f001d_11501 desc),zdf=(select top 1 f007n_11501 from tb_trade_11501 where f003v_11501=a.f003v_11501 order by f001d_11501 desc) from tb_trade_11501 as a where f003v_11501 in('道琼斯指数','纳斯达克指数') union ");
            sql.Append(" select distinct name=f003v_11503,zxz=(select top 1 f005n_11503 from tb_trade_11503 where f003v_11503=a.f003v_11503 order by f001d_11503 desc),zdf=(select top 1 f007n_11503 from tb_trade_11503 where f003v_11503=a.f003v_11503 order by f001d_11503 desc) from tb_trade_11503 as a where f003v_11503 in('日经225指数','香港恒生指数') union ");
            sql.Append(" select distinct name=f003v_11502,zxz=(select top 1 f005n_11502 from tb_trade_11502 where f003v_11502=a.f003v_11502 order by f001d_11502 desc),zdf=(select top 1 f007n_11502 from tb_trade_11502 where f003v_11502=a.f003v_11502 order by f001d_11502 desc) from tb_trade_11502 as a where f003v_11502 in('英金融时报指数','德国法兰克福DAX指数') union ");
            sql.Append(" select distinct name=f003v_11508,zxz=(select top 1 f005n_11508 from tb_trade_11508 where f003v_11508=a.f003v_11508 order by f001d_11508 desc),zdf=(select top 1 f007n_11508 from tb_trade_11508 where f003v_11508=a.f003v_11508 order by f001d_11508 desc) from tb_trade_11508 as a where f003v_11508 in('美元日元','美元人民币','欧元美元','澳元美元') union ");
            sql.Append(" select distinct name=f003v_11505,zxz=(select top 1 f005n_11505 from tb_trade_11505 where f003v_11505=a.f003v_11505 order by f001d_11505 desc),zdf=(select top 1 f007n_11505 from tb_trade_11505 where f003v_11505=a.f003v_11505 order by f001d_11505 desc) from tb_trade_11505 as a where f003v_11505 in('LmeS_铜3','LmeS_铝3','LmeS_锡3','LmeS_镍3','LmeS_锌3','LmeS_铅3','美豆指数','美玉米指','美小麦指','美豆油指','糖11指数','美棉指数') union ");

            sql.Append(" select distinct name='美豆粕05',zxz=(select top 1 f005n_11505 from tb_trade_11505 where f003v_11505 like '美豆粕%' order by f001d_11505 desc),zdf=(select top 1 f007n_11505 from tb_trade_11505 where f003v_11505 like '美豆粕%' order by f001d_11505 desc) from tb_trade_11505 as a where f003v_11505 like '美豆粕%' union ");

            sql.Append(" select distinct name='美原油04',zxz=(select top 1 f005n_11505 from tb_trade_11505 where f003v_11505 like '美原油%' order by f001d_11505 desc),zdf=(select top 1 f007n_11505 from tb_trade_11505 where f003v_11505 like '美原油%' order by f001d_11505 desc) from tb_trade_11505 as a where f003v_11505 like '美原油%' union ");

            sql.Append(" select distinct name='美燃油04',zxz=(select top 1 f005n_11505 from tb_trade_11505 where f003v_11505 like '美燃油%' order by f001d_11505 desc),zdf=(select top 1 f007n_11505 from tb_trade_11505 where f003v_11505 like '美燃油%' order by f001d_11505 desc) from tb_trade_11505 as a where f003v_11505 like '美燃油%' union ");

            sql.Append(" select distinct name=f003v_11507,zxz=(select top 1 f005n_11507 from tb_trade_11507 where f003v_11507=a.f003v_11507 order by f001d_11507 desc),zdf=(select top 1 f007n_11507 from tb_trade_11507 where f003v_11507=a.f003v_11507 order by f001d_11507 desc) from tb_trade_11507 as a where f003v_11507 in ('美黄金指','白银指数')");
            return instance(DBConn.NewDMSConnstr, CommandType.Text, sql.ToString());
        }
        #endregion
    }
}
